package net.bat.service.dlr;

import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.shiro.SecurityUtils;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
import org.springframework.data.domain.Sort.Direction;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;
import org.springside.modules.persistence.DynamicSpecifications;
import org.springside.modules.persistence.SearchFilter;
import org.springside.modules.persistence.SearchFilter.Operator;

import net.bat.dao.QueryResult;
import net.bat.dao.UserDAO;
import net.bat.dto.EDlrVO;
import net.bat.dto.LawsuitsDlrVO;
import net.bat.entity.EDlr;
import net.bat.entity.EDlrSh;
import net.bat.entity.SDlr;
import net.bat.entity.SDlrAjsl;
import net.bat.entity.SFswx;
import net.bat.entity.User;
import net.bat.filter.ExtReq;
import net.bat.repository.DlrDao;
import net.bat.service.account.ShiroDbRealm.ShiroUser;
import net.bat.web.api.ApiController;
import net.bat.web.dlr.SDlrDao;

//Spring Bean的标识.
@Component
//类中所有public函数都纳入事务管理的标识.
@Transactional
public class DlrService {
	@Autowired
	private DlrDao dlrDao;
	
	@Autowired
	private SDlrDao sdlrDao;
	
	@Autowired
	private UserDAO dao;
	
	@Value("${url.dlr.attach}")
	private String url_dlr_attach;
	
	public String getUrlDlrAttach(){
		return url_dlr_attach;
	}
	
	/**
	 * 填报代理人审核状态
	 * @author wangshuxin
	 * 2016-04-18
	 */
	public static final String PN_SHZT = "shzt";
	/**
	 * 填报代理人实体名
	 * @author wangshuxin
	 * 2016-04-18
	 */
	public static final String EN_DLRSH = "EDlrSh";
	
	public EDlr getDlr(Long id) {
		return dlrDao.findOne(id);
	}
	//Long userId, Map<String, Object> searchParams,
//	public Page<EDlr> getDlr( Map<String, Object> searchParams, int pageNumber, int pageSize, String sortType){
//		PageRequest pageRequest = buildPageRequest(pageNumber, pageSize, sortType);
//		Map<String, SearchFilter> filters = SearchFilter.parse(searchParams);
//		Specification<EDlr> spec = DynamicSpecifications.bySearchFilter(filters.values(), EDlr.class);
//
//		Page<EDlr> r = dlrDao.findAll(spec, pageRequest);
//		return r;
//	}
	
//	public Page<EDlrDTO> getDlrPage(Map<String, Object> searchParams,Map<String,Object> eparas, 
//			int pageNumber, int pageSize, String sortType,boolean bAsc)  {
//		PageRequest pageRequest = buildPageRequest(pageNumber, pageSize, sortType, bAsc);
//		Map<String, SearchFilter> filters = SearchFilter.parse(searchParams);
//		String selectjpql = "select new net.bat.dto.EDlrDTO(a.id, a.xm,a.dtGzjy,a.wynl, b.mc,b.dy,a.sxzy,a.msss,a.xzss,c.sl)  from EDlr a, EDljg b, SDlr c";
//		String selectjpql_cout="select count(*) from EDlr a, EDljg b, SDlr c";
//		//TODO 构造where条件和参数值
//		String wherejpql = "a.pid = b.id and c.pid = a.id  and c.fl=(?1) and c.nd = (?2) ";
//		List<Object> queryParams = new ArrayList<Object>();
//		String sfl = (String) searchParams.get("EQ_fl");
//		queryParams.add(sfl);
//		queryParams.add(0);
//		int flag = 3;
//		
//		if(eparas.containsKey("sl_fwlx")){
//			String value = (String) eparas.get("sl_fwlx");
//			if(value.equals("zlsq")){
//				//不做处理
//			}
//			if(value.equals("zlfs")){
//				 queryParams.clear();
//				 selectjpql = "select new net.bat.dto.EDlrDTO(a.id, a.xm,a.dtGzjy,a.wynl, b.mc,b.dy,a.sxzy,a.msss,a.xzss,c.sl)  from EDlr a, EDljg b, SDljgFswx c";
//				 selectjpql_cout="select count(*) from EDlr a, EDljg b, SDljgFswx c";
//				//TODO 构造where条件和参数值
//				 wherejpql = " a.pid = b.id and c.pid = b.id and c.fl = (?1) ";
//				 queryParams.add(1);
//				 flag = 2;
//			}
//			if(value.equals("zlwx")){
//				 queryParams.clear();
//				 selectjpql = "select new net.bat.dto.EDlrDTO(a.id, a.xm,a.dtGzjy,a.wynl, b.mc,b.dy,a.sxzy,a.msss,a.xzss,c.sl)  from EDlr a, EDljg b, SDljgFswx c";
//				 selectjpql_cout="select count(*) from EDlr a, EDljg b, SDljgFswx c";
//				//TODO 构造where条件和参数值
//				 wherejpql = " a.pid = b.id and c.pid = b.id and c.fl = (?1) ";
//				 queryParams.add(2);
//				 flag = 2;
//			}
//			if(value.equals("zlxzss")){
//				 queryParams.clear();
//				 selectjpql = "select new net.bat.dto.EDlrDTO(a.id, a.xm,a.dtGzjy,a.wynl, b.mc,b.dy,a.sxzy,a.msss,a.xzss,c.xzajsl)  from EDlr a, EDljg b, SDlrAjsl c";
//				 selectjpql_cout="select count(*) from EDlr a, EDljg b, SDlrAjsl c";
//				//TODO 构造where条件和参数值
//				 wherejpql = " a.pid = b.id and c.pid = a.id and c.jgdm = b.jgdm ";
//				 flag = 1;
//				 
//			}
//			if(value.equals("zlmsss")){
//				 queryParams.clear();
//				 selectjpql = "select new net.bat.dto.EDlrDTO(a.id, a.xm,a.dtGzjy,a.wynl, b.mc,b.dy,a.sxzy,a.msss,a.xzss,c.msajsl)  from EDlr a, EDljg b, SDlrAjsl c";
//				 selectjpql_cout="select count(*) from EDlr a, EDljg b, SDlrAjsl c";
//				//TODO 构造where条件和参数值
//				 wherejpql = " a.pid = b.id and c.pid = a.id and c.jgdm = b.jgdm ";
//				 flag = 1;
//			}
//			if(value.equals("zlqsjf")){
//				wherejpql += " and a.zlqsjf > (?"+ flag++ +") ";
//				queryParams.add(new Integer(0));
//			}
//			if(value.equals("xzzfaj")){
//				wherejpql += " and a.xzzfaj > (?"+ flag++ +") ";
//				queryParams.add(new Integer(0));
//			}
//			if(value.equals("zljs")){
//				wherejpql += " and a.zljs > (?"+ flag++ +") ";
//				queryParams.add(new Integer(0));
//			}
//			if(value.equals("zlfxpj")){
//				wherejpql += " and a.zlfxpj > (?"+ flag++ +") ";
//				queryParams.add(new Integer(0));
//			}
//			if(value.equals("zlyj")){
//				wherejpql += " and a.zlyj > (?"+ flag++ +") ";
//				queryParams.add(new Integer(0));
//			}
//			if(value.equals("zlyy")){
//				wherejpql += " and a.zlyy > (?"+ flag++ +") ";
//				queryParams.add(new Integer(0));
//			}
//			if(value.equals("zldh")){
//				wherejpql += " and a.zldh > (?"+ flag++ +") ";
//				queryParams.add(new Integer(0));
//			}
//			if(value.equals("zscqgb")){
//				wherejpql += " and a.zscqgb > (?"+ flag++ +") ";
//				queryParams.add(new Integer(0));
//			}
//		}
//		
//		
//		
//		if(eparas.containsKey("dy")){
//			String value = (String) eparas.get("dy");
//			String[] dys = value.split("_");
//			String sdys = " and (";
//			for(String str : dys){
//				sdys += " b.dy like (?"+ flag++ +") or ";
//				queryParams.add("%" + str + "%");
//			}
//			
//			wherejpql += sdys.substring(0, sdys.lastIndexOf("or")) + ")";
//		}
//		if(eparas.containsKey("sl_zz")){
//			String value = (String) eparas.get("sl_zz");
//			wherejpql += " and a.zz = (?" + flag++ + ") ";
//			queryParams.add(Integer.parseInt(value.substring(0)));
//		}
//		if(eparas.containsKey("sl_gzwy")){
//			String value = (String) eparas.get("sl_gzwy");
//			String[] dys = value.split(";");
//			String sdys = " and (";
//			for(String str : dys){
//				
//				sdys += " a.wynl like (?"+ flag++ +") or ";
//				if(value.equals("1")){
//					queryParams.add("%" + "英语" + "%");
//				} else if(value.equals("2")){
//					queryParams.add("%" + "日语" + "%");
//				}else if(value.equals("3")){
//					queryParams.add("%" + "韩语" + "%");
//				}else if(value.equals("4")){
//					queryParams.add("%" + "德语" + "%");
//				}else if(value.equals("5")){
//					queryParams.add("%" + "法语" + "%");
//				}else if(value.equals("6")){
//					queryParams.add("%" + "" + "%");
//				}
//			}
//			wherejpql += sdys.substring(0, sdys.lastIndexOf("or")) + ")";
//		}
//		if(eparas.containsKey("sl_zynx")){
//			String value = (String) eparas.get("sl_zynx");
//			if(value.contains("lt")){
//				Calendar calendar = Calendar.getInstance(); 
//				Date date = calendar.getTime();
//				int year = calendar.get(Calendar.YEAR);
//				calendar.set(year - Integer.parseInt(value.substring(0,value.indexOf("lt"))), 0, 0);
//				wherejpql += " and a.dtGzjy < (?" + flag++ + ") ";
//				wherejpql += " and a.dtGzjy > (?" + flag++ + ") ";
//				queryParams.add(date);
//				queryParams.add(calendar.getTime());
//			}
//			if(value.contains("-")){
//				Calendar firstcalendar = Calendar.getInstance(); 
//				Calendar secondcalendar = Calendar.getInstance(); 
//				int year = firstcalendar.get(Calendar.YEAR);
//				firstcalendar.set(year - Integer.parseInt(value.split("-")[1]), 0, 0);
//				secondcalendar.set(year - Integer.parseInt(value.split("-")[0]), 0, 0);
//				
//				wherejpql += " and a.dtGzjy <= (?" + flag++ + ") ";
//				wherejpql += " and a.dtGzjy > (?" + flag++ + ") ";
//				queryParams.add(secondcalendar.getTime());
//				queryParams.add(firstcalendar.getTime());
//			}
//			if(value.contains("gt")){
//				Calendar calendar = Calendar.getInstance(); 
//				Date date = calendar.getTime();
//				int year = calendar.get(Calendar.YEAR);
//				calendar.set(year - Integer.parseInt(value.substring(0,value.indexOf("gt"))), 0, 0);
//				wherejpql += " and a.dtGzjy <= (?" + flag++ + ") ";
//				queryParams.add(calendar.getTime());
//				
//			}
//			
//		}
//		
//		
//		if(eparas.containsKey("name")){
//			String value = (String) eparas.get("name");
//			wherejpql += " and a.xm like  (?"+ flag++ +") ";
//			queryParams.add("%"+value.trim()+"%");
//		}
//		if(eparas.containsKey("sl_pjqlx")){
//			String value = (String) eparas.get("sl_pjqlx");
//			if(value.contains("lt")){
//				wherejpql += " and c.sl < (?" + flag++ + ") ";
//				queryParams.add(Integer.parseInt(value.substring(0,value.indexOf("lt"))));
//			}
//			if(value.contains("-")){
//				wherejpql += " and c.sl >= (?" + flag++ + ") ";
//				wherejpql += " and c.sl < (?" + flag++ + ") ";
//				queryParams.add(Integer.parseInt(value.split("-")[0]));
//				queryParams.add(Integer.parseInt(value.split("-")[1]));
//			}
//			if(value.contains("gt")){
//				wherejpql += " and c.sl >= (?" + flag++ + ") ";
//				queryParams.add(Integer.parseInt(value.substring(0,value.indexOf("gt"))));
//			}
//		}
//		
//		if(eparas.containsKey("sl_smsys")){
//			String value = (String) eparas.get("sl_smsys");
//			if(value.contains("lt")){
//				wherejpql += " and c.sl < (?" + flag++ + ") ";
//				queryParams.add(Integer.parseInt(value.substring(0,value.indexOf("lt"))));
//			}
//			if(value.contains("-")){
//				wherejpql += " and c.sl >= (?" + flag++ + ") ";
//				wherejpql += " and c.sl < (?" + flag++ + ") ";
//				queryParams.add(Integer.parseInt(value.split("-")[0]));
//				queryParams.add(Integer.parseInt(value.split("-")[1]));
//			}
//			if(value.contains("gt")){
//				wherejpql += " and c.sl >= (?" + flag++ + ") ";
//				queryParams.add(Integer.parseInt(value.substring(0,value.indexOf("gt"))));
//			}
//		}
//		
//		if(eparas.containsKey("sl_dll")){
//			String value = (String) eparas.get("sl_dll");
//			if(value.contains("lt")){
//				if(eparas.containsKey("sl_fwlx")){
//					String temp = (String) eparas.get("sl_fwlx");
//					if(temp.equals("zlxzss")){
//						 wherejpql += " and c.xzajsl >= (?" + flag++ + ") ";
//					}else if(temp.equals("zlmsss")){
//						 wherejpql += " and c.msajsl >= (?" + flag++ + ") ";
//					}else{
//						wherejpql += " and c.sl < (?" + flag++ + ") ";
//					}
//				}else{
//					wherejpql += " and c.sl < (?" + flag++ + ") ";
//				}
//				queryParams.add(Integer.parseInt(value.substring(0,value.indexOf("lt"))));
//			}
//			if(value.contains("-")){
//				if(eparas.containsKey("sl_fwlx")){
//					String temp = (String) eparas.get("sl_fwlx");
//					if(temp.equals("zlxzss")){
//						 wherejpql += " and c.xzajsl >= (?" + flag++ + ") ";
//						 wherejpql += " and c.xzajsl < (?" + flag++ + ") ";
//					}else if(temp.equals("zlmsss")){
//						 wherejpql += " and c.msajsl >= (?" + flag++ + ") ";
//						 wherejpql += " and c.msajsl < (?" + flag++ + ") ";
//					}else{
//						wherejpql += " and c.sl >= (?" + flag++ + ") ";
//						wherejpql += " and c.sl < (?" + flag++ + ") ";
//					}
//				}else{ 
//					wherejpql += " and c.sl >= (?" + flag++ + ") ";
//					wherejpql += " and c.sl < (?" + flag++ + ") ";
//				}
//				queryParams.add(Integer.parseInt(value.split("-")[0]));
//				queryParams.add(Integer.parseInt(value.split("-")[1]));
//			}
//			if(value.contains("gt")){
//				if(eparas.containsKey("sl_fwlx")){
//					String temp = (String) eparas.get("sl_fwlx");
//					if(temp.equals("zlxzss")){
//						 wherejpql += " and c.xzajsl >= (?" + flag++ + ") ";
//					}else if(temp.equals("zlmsss")){
//						 wherejpql += " and c.msajsl >= (?" + flag++ + ") ";
//					}else{
//						wherejpql += " and c.sl >= (?" + flag++ + ") ";
//					}
//				}else{
//					wherejpql += " and c.sl >= (?" + flag++ + ") ";
//				}
//				queryParams.add(Integer.parseInt(value.substring(0,value.indexOf("gt"))));
//				
//			}
//		}
//		
//		
//	System.out.println(selectjpql + wherejpql);
//		String orderby=null;
//		if(sortType!=null){
//			if(sortType.equals("auto")){
//				orderby = "a.id";
//			}else{
//				if(bAsc){
//					orderby = sortType;
//				}else{
//					orderby = sortType +" desc";
//				}
//			}
//		}
//		QueryResult<Map> qr = dao.getScrollData(selectjpql, selectjpql_cout, 
//				(pageNumber-1)*pageSize, pageSize, wherejpql, queryParams.toArray(), orderby);
//		Page p = new PageImpl(qr.getResultlist(), pageRequest, qr.getTotalrecord());
//		return p;
//	}

	
	public Page<EDlrVO> getDlrPage(Map<String, Object> searchParams,Map<String,Object> eparas, 
			int pageNumber, int pageSize, String sortType,boolean bAsc)  {
		PageRequest pageRequest = buildPageRequest(pageNumber, pageSize, sortType, bAsc);		
		StringBuffer sql = new StringBuffer("select a.id,a.xm,a.dt_gzjy,a.wynl,b.jgdm,b.mc,b.dy,a.sxzy,c.sl from e_dlr a left join e_dljg b on a.pid = b.id left join s_dlr c on ");
		String count = "select count(*) from e_dlr a left join e_dljg b  on a.pid = b.id left join s_dlr c on ";
		StringBuffer where = new StringBuffer(); 
		StringBuffer condition = new StringBuffer(" where 1=1 ");
		String sfl = (String) searchParams.get("EQ_fl");
		//c.pid = a.id and c.fl = '100000' and c.nd = 0 
		where.append(" c.fl = " + sfl).append(" and  c.nd = 0 ").append(" and c.pid = a.id ");
		
		/*********************************************************/
		//通过给出机构pid值找出该机构下的所有的代理人
		if(eparas.containsKey("jg")){
			String value = (String) eparas.get("jg");
			if(value != null && !"".equals(value)){
				condition.append(" and b.id = " + value.trim());
			}
		}
		
		/*********************************************************/
		
		if(eparas.containsKey("dy")){
			String value = (String) eparas.get("dy");
			String[] dys = value.split("_");
			String sdys = " and (";
			for(String str : dys){
				sdys += " b.dy like ("+ "'%" + str + "%'" +") or ";
			}
			condition.append(sdys.substring(0, sdys.lastIndexOf("or")) + ")");
		}
		
		if(eparas.containsKey("name")){
		String value = (String) eparas.get("name");
		condition.append(" and a.xm like " + "'%" + value.trim() + "%'" );
		}
		
		if(eparas.containsKey("sl_zynx")){
			String value = (String) eparas.get("sl_zynx");
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			if(value.contains("lt")){
				Calendar calendar = Calendar.getInstance(); 
				String first = sdf.format(calendar.getTime());
				condition.append(" and a.dt_gzjy < " + "'" + first + "'");
				int year = calendar.get(Calendar.YEAR);
				int month = calendar.get(Calendar.MONTH) + 1;
				calendar.set(year - Integer.parseInt(value.substring(0,value.indexOf("lt"))), month, 0);
				String second = sdf.format(calendar.getTime());
				condition.append(" and a.dt_gzjy > " + "'"+second +"'");
			}else if(value.contains("-")){
				Calendar firstcalendar = Calendar.getInstance(); 
				Calendar secondcalendar = Calendar.getInstance(); 
				int year = firstcalendar.get(Calendar.YEAR);
//				int month = firstcalendar.get(Calendar.MONTH) + 1;
//				int day = firstcalendar.get(Calendar.DAY_OF_MONTH);
				firstcalendar.set(year - Integer.parseInt(value.split("-")[1]), 0, 0);
				secondcalendar.set(year - Integer.parseInt(value.split("-")[0]), 0, 0);
				String first = sdf.format(firstcalendar.getTime());
				String second = sdf.format(secondcalendar.getTime());
				condition.append(" and a.dt_gzjy > " + "'" + first + "'");
				condition.append(" and a.dt_gzjy <= " + "'"+second +"'");
			}else if(value.contains("gt")){
				Calendar calendar = Calendar.getInstance(); 
				int year = calendar.get(Calendar.YEAR);
				calendar.set(year - Integer.parseInt(value.substring(0,value.indexOf("gt"))), 0, 0);
				String first = sdf.format(calendar.getTime());
				condition.append(" and a.dt_gzjy <= " + "'" + first + "'");
			}
			
		}
		if(eparas.containsKey("sl_zz")){
			String value = (String) eparas.get("sl_zz");
			condition.append(" and a.zz = " + value);
		}
		if(eparas.containsKey("sl_gzwy")){
		String value = (String) eparas.get("sl_gzwy");
		if(value.equals("1")){
			condition.append(" and a.wynl like ("+ "'%" + "英语" + "%'" +") ");
		} else if(value.equals("2")){
			condition.append(" and a.wynl like ("+ "'%" + "日语" + "%'" +") ");
		}else if(value.equals("3")){
			condition.append(" and a.wynl like ("+ "'%" + "韩语" + "%'" +") ");
		}else if(value.equals("4")){
			condition.append(" and a.wynl like ("+ "'%" + "德语" + "%'" +") ");
		}else if(value.equals("5")){
			condition.append(" and a.wynl like ("+ "'%" + "法语" + "%'" +") ");
		}else if(value.equals("6")){
			condition.append(" and a.wynl like ("+ "'%" + "其他" + "%'" +") ");
		}
	}
		
		if(eparas.containsKey("sl_fwlx")){
			String value = (String) eparas.get("sl_fwlx");
			if(value.equals("zlsq")){
				condition.append(" and  c.sl > 0 ");
			}
			if(value.equals("zlfs")){
				sql  = null ;
				count = null; 
				where = null;
				sql = new StringBuffer("select a.id,a.xm,a.dt_gzjy,a.wynl,b.mc,b.dy,a.sxzy,c.sl from e_dlr a left join e_dljg b on a.pid = b.id left join s_dlr_fswx c on ");
				// selectjpql = "select new net.bat.dto.EDljgDTO(a.id, a.jgdm, a.mc, b.sl, a.dtSlsj, a.dlrrs, a.dy)  from EDljg a, SDljgFswx b";
				count = "select count(*)  from e_dlr a left join e_dljg b on a.pid = b.id left join s_dlr_fswx c on ";
				//TODO 构造where条件和参数值
				where = new StringBuffer(" c.pid = a.id and c.fl = 1 ");
				condition.append(" and  c.sl > 0 ");
			}
			if(value.equals("zlwx")){
				sql  = null ;
				count = null;
				where = null;
				sql = new StringBuffer("select a.id,a.xm,a.dt_gzjy,a.wynl,b.mc,b.dy,a.sxzy,c.sl from e_dlr a left join e_dljg b on a.pid = b.id left join s_dlr_fswx c on ");
					// selectjpql = "select new net.bat.dto.EDljgDTO(a.id, a.jgdm, a.mc, b.sl, a.dtSlsj, a.dlrrs, a.dy)  from EDljg a, SDljgFswx b";
				count = "select count(*) from e_dlr a left join e_dljg b on a.pid = b.id left join s_dlr_fswx c on ";
					//TODO 构造where条件和参数值
				where = new StringBuffer(" c.pid = a.id and c.fl = 2 ");
				condition.append(" and  c.sl > 0 ");
			}
			if(value.equals("zlxzss")){
				sql  = null ;
				count = null;
				where = null;
				sql = new StringBuffer("select a.id,a.xm,a.dt_gzjy,a.wynl,b.mc,b.dy,a.sxzy,c.xzajsl from e_dlr a left join e_dljg b on a.pid = b.id left join s_dlr_ajsl c on ");
					// selectjpql = "select new net.bat.dto.EDljgDTO(a.id, a.jgdm, a.mc, b.sl, a.dtSlsj, a.dlrrs, a.dy)  from EDljg a, SDljgFswx b";
				count = "select count(*) from e_dlr a left join e_dljg b on a.pid = b.id left join s_dlr_ajsl c on  ";
					//TODO 构造where条件和参数值
				where = new StringBuffer(" a.id = c.pid ");
				if("c.sl".equals(sortType)){
						 sortType = "c.xzajsl";
				}
				condition.append(" and  c.xzajsl > 0 ");
			}
			if(value.equals("zlmsss")){
				sql  = null ;
				count = null;
				where = null;
				sql = new StringBuffer("select a.id,a.xm,a.dt_gzjy,a.wynl,b.mc,b.dy,a.sxzy,c.msajsl from e_dlr a left join e_dljg b on a.pid = b.id left join s_dlr_ajsl c on ");
						// selectjpql = "select new net.bat.dto.EDljgDTO(a.id, a.jgdm, a.mc, b.sl, a.dtSlsj, a.dlrrs, a.dy)  from EDljg a, SDljgFswx b";
				count = "select count(*) from e_dlr a left join e_dljg b on a.pid = b.id left join s_dlr_ajsl c on  ";
						//TODO 构造where条件和参数值
				where = new StringBuffer(" a.id = c.pid ");
				if("c.sl".equals(sortType)){
						 sortType = "c.msajsl";
				}
				condition.append(" and  c.msajsl > 0 ");
			}
			if(value.equals("zlqsjf")){
				condition.append(" and a.zlqsjf > " + '0');
				condition.append(" and  c.sl > 0 ");
			}
			if(value.equals("xzzfaj")){
				condition.append(" and a.xzzfaj > " + '0');
				condition.append(" and  c.sl > 0 ");
			}
			if(value.equals("zljs")){
				condition.append(" and a.zljs > " + '0');
				condition.append(" and  c.sl > 0 ");
			}
			if(value.equals("zlfxpy")){
				condition.append(" and a.zlfxpj > " + '0');
				condition.append(" and  c.sl > 0 ");
			}
			if(value.equals("zlyj")){
				condition.append(" and a.zlyj > " + '0');
				condition.append(" and  c.sl > 0 ");
			}
			if(value.equals("zlyy")){
				condition.append(" and a.zlyy > " + '0');
				condition.append(" and  c.sl > 0 ");
			}
			if(value.equals("zldh")){
				condition.append(" and a.zldh > " + '0');
				condition.append(" and  c.sl > 0 ");
			}
			if(value.equals("zscqgb")){
				condition.append(" and a.zscqgb > " + '0');
				condition.append(" and  c.sl > 0 ");
			}
		}

		if(eparas.containsKey("sl_pjqlx")){
			String value = (String) eparas.get("sl_pjqlx");
			if(value.contains("lt")){
				condition.append(" and c.sl < " + value.substring(0,value.indexOf("lt")));
			}
			if(value.contains("-")){
				condition.append(" and c.sl >= " + value.split("-")[0]);
				condition.append(" and c.sl < " + value.split("-")[1]);
			}
			if(value.contains("gt")){
				condition.append(" and c.sl >= " + value.substring(0,value.indexOf("gt")));
			}
		}
		
		if(eparas.containsKey("sl_smsys")){
			String value = (String) eparas.get("sl_smsys");
			if(value.contains("lt")){
				condition.append(" and c.sl < " + value.substring(0,value.indexOf("lt")));
			}
			if(value.contains("-")){
				condition.append(" and c.sl >= " + value.split("-")[0]);
				condition.append(" and c.sl < " + value.split("-")[1]);
			}
			if(value.contains("gt")){
				condition.append(" and c.sl >= " + value.substring(0,value.indexOf("gt")));
			}
		}
		
		if(eparas.containsKey("sl_dll")){
			String value = (String) eparas.get("sl_dll");
			if(value.contains("lt")){
				if(eparas.containsKey("sl_fwlx")){
					String temp = (String) eparas.get("sl_fwlx");
					String pjqlx = (String)searchParams.get("sl_pjqlx");
					String smsys = (String)searchParams.get("sl_smsys");
					if(temp.equals("zlxzss")){
						 condition.append(" and c.xzajsl < " + value.substring(0,value.indexOf("lt")));
					}else if(temp.equals("zlmsss")){
						 condition.append(" and c.msajsl < " + value.substring(0,value.indexOf("lt")));
					}else if(pjqlx != null || smsys != null){
						sql  = null ;
						count = null;
						where = null;
						condition = null; 
						sql = new StringBuffer("select a.id,a.xm,a.dt_gzjy,a.wynl,b.mc,b.dy,a.sxzy,c.sl from e_dlr a left join e_dljg b on a.pid = b.id left join s_dlr c on ");
						 count = "select count(*) from e_dlr a left join e_dljg b on a.pid = b.id left join s_dlr c on ";
						 where = new StringBuffer();
						 where.append(" c.nd = 0 ").append(" and c.pid = a.id ");
						 condition = new StringBuffer(" where 1 = 1 ");
						 condition.append(" and  (CASE WHEN  ");
						 String fl = (String) searchParams.get("EQ_fl");
						 String dllfl = "100" + fl.substring(3) ;
						 condition.append(" c.fl= " + dllfl).append(" and c.sl < " + value.substring(0,value.indexOf("lt")) + " then 1 when ");
					 	String values = null;
					 	if(pjqlx != null){
					 		values = (String) eparas.get("sl_pjqlx");
					 	}
					 	if(smsys != null){
					 		values = (String) eparas.get("sl_smsys");
					 	}
						if(values.contains("lt")){
							condition.append(" c.fl =  "  + fl +   " and c.sl < " + values.substring(0,values.indexOf("lt")) + " then 1 else 0 end ) = 1");
						}
						if(values.contains("-")){
							condition.append(" c.fl =  "  + fl +   " and c.sl >= " + values.split("-")[0] + " and c.sl < " + values.split("-")[1] +" then 1 else 0 end ) = 1");
						}
						if(values.contains("gt")){
							condition.append(" c.fl =  "  + fl +   " and c.sl >= " + values.substring(0,values.indexOf("gt")) + " then 1 else 0 end ) = 1");
						}
					}else{
						condition.append(" and c.sl < " + value.substring(0,value.indexOf("lt")));
					}
				}else{
					condition.append(" and c.sl < " + value.substring(0,value.indexOf("lt")));
				}
			}
			if(value.contains("-")){
				if(eparas.containsKey("sl_fwlx")){
					String temp = (String) eparas.get("sl_fwlx");
					String pjqlx = (String)searchParams.get("sl_pjqlx");
					String smsys = (String)searchParams.get("sl_smsys");
					if(temp.equals("zlxzss")){
						 condition.append(" and c.xzajsl >= " + value.split("-")[0]);
						 condition.append(" and c.xzajsl < " + value.split("-")[1]);
					}else if(temp.equals("zlmsss")){
						 condition.append(" and c.msajsl >= " + value.split("-")[0]);
						 condition.append(" and c.msajsl < " + value.split("-")[1]);
					}else if(pjqlx != null || smsys != null){
						sql  = null ;
						count = null;
						where = null;
						condition = null; 
						sql = new StringBuffer("select a.id,a.xm,a.dt_gzjy,a.wynl,b.mc,b.dy,a.sxzy,c.sl from e_dlr a left join e_dljg b on a.pid = b.id left join s_dlr c on ");
						 count = "select count(*) from e_dlr a left join e_dljg b on a.pid = b.id left join s_dlr c on ";
						 where = new StringBuffer();
						 where.append(" c.nd = 0 ").append(" and c.pid = a.id ");
						 condition = new StringBuffer(" where 1 = 1 ");
						 condition.append(" and  (CASE WHEN  ");
						 String fl = (String) searchParams.get("EQ_fl");
						 String dllfl = "100" + fl.substring(3) ;
						 condition.append(" c.fl= " + dllfl).append(" and c.sl >=  " + value.split("-")[0]+" and c.sl <  " + value.split("-")[1] + " then 1 when ");
					 	String values = null;
					 	if(pjqlx != null){
					 		values = (String) eparas.get("sl_pjqlx");
					 	}
					 	if(smsys != null){
					 		values = (String) eparas.get("sl_smsys");
					 	}
						if(values.contains("lt")){
							condition.append(" c.fl =  "  + fl +   " and c.sl < " + values.substring(0,values.indexOf("lt")) + " then 1 else 0 end ) = 1");
						}
						if(values.contains("-")){
							condition.append(" c.fl =  "  + fl +   " and c.sl >= " + values.split("-")[0] + " and c.sl < " + values.split("-")[1] +" then 1 else 0 end ) = 1");
						}
						if(values.contains("gt")){
							condition.append(" c.fl =  "  + fl +   " and c.sl >= " + values.substring(0,values.indexOf("gt")) + " then 1 else 0 end ) = 1");
						}
					}else{
						condition.append(" and c.sl >= " + value.split("-")[0]);
						condition.append(" and c.sl < " + value.split("-")[1]);
					}
				}else{ 
					condition.append(" and c.sl >= " + value.split("-")[0]);
					condition.append(" and c.sl < " + value.split("-")[1]);
				}
			}
			if(value.contains("gt")){
				if(eparas.containsKey("sl_fwlx")){
					String temp = (String) eparas.get("sl_fwlx");
					if(temp.equals("zlxzss")){
						condition.append(" and c.xzajsl >= " + value.substring(0,value.indexOf("gt")));
					}else if(temp.equals("zlmsss")){
						 condition.append(" and c.msajsl >= " + value.substring(0,value.indexOf("gt")));
					}else{
						condition.append(" and c.sl >= " + value.substring(0,value.indexOf("gt")));
					}
				}else{
				condition.append(" and c.sl >= " + value.substring(0,value.indexOf("gt")));
				}
				
			}
		}
		String orderby=null;
		if(sortType!=null){
			if(sortType.equals("auto")){
				orderby = "a.id";
			}else{
				if(bAsc){
					orderby = sortType;
					if(sortType.equals("a.dt_gzjy")){
						orderby = sortType +" desc";
					}
				}else{
					orderby = sortType +" desc";
					if(sortType.equals("a.dt_gzjy")){
						orderby = sortType;
					}
				}
			}
		}
		QueryResult<EDlrVO> qr = dao.getScrollDataForDlr(sql.toString(),count,where.toString(),(pageNumber-1)*pageSize, pageSize,condition.toString(),orderby);
		Page p = new PageImpl(qr.getResultlist(), pageRequest, qr.getTotalrecord());
		return p;
	}
	
	
	
	
	
//	public Page<LawsuitsDlrDTO> getLawSuitsDlrPage(Map<String, Object> searchParams,Map<String,Object> eparas, 
//			int pageNumber, int pageSize, String sortType,boolean bAsc)  {
//		PageRequest pageRequest = buildPageRequest(pageNumber, pageSize, sortType, bAsc);
//		Map<String, SearchFilter> filters = SearchFilter.parse(searchParams);
//		String selectjpql = "select new net.bat.dto.LawsuitsDlrDTO(a.id, a.xm,a.dtGzjy,a.wynl, b.mc,b.dy,a.jsly,c.msajsl,c.xzajsl,a.sxzy)  from EDlr a, EDljg b, SDlrAjsl c, SDlr d ";
//		String selectjpql_cout="select count(*) from EDlr a, EDljg b, SDlrAjsl c, SDlr d";
//		//TODO 构造where条件和参数值
//		String wherejpql = "a.pid = b.id and c.pid = a.id and d.pid = a.id and d.fl=(?1) and d.nd = (?2)";
//		List<Object> queryParams = new ArrayList<Object>();
//		String sfl = (String) searchParams.get("EQ_fl");
//		queryParams.add(sfl);
//		queryParams.add(0);
//		int flag = 3;
//		Set<String> keys = eparas.keySet();
//		
//		
//		if(eparas.containsKey("sl_fwlx")){
//			String value = (String) eparas.get("sl_fwlx");
//			if(value.equals("zlsq")){
//				
//				
//			}
//			if(value.equals("zlfs")){
//				queryParams.clear();
//				selectjpql = "select new net.bat.dto.LawsuitsDlrDTO(a.id, a.xm,a.dtGzjy,a.wynl, b.mc,b.dy,a.jsly,c.msajsl,c.xzajsl,a.sxzy)  from EDlr a, EDljg b, SDlrAjsl c,SDlrFswx d ";
//				selectjpql_cout="select count(*) from EDlr a, EDljg b, SDlrAjsl c, SDlrFswx d";
//				//TODO 构造where条件和参数值
//				wherejpql = "a.pid = b.id and c.pid = a.id and d.pid = a.id and d.fl=(?1) and d.sl > 0";
//				queryParams.add(1);
//				flag = 2;
//			}
//			if(value.equals("zlwx")){
//				queryParams.clear();
//				selectjpql = "select new net.bat.dto.LawsuitsDlrDTO(a.id, a.xm,a.dtGzjy,a.wynl, b.mc,b.dy,a.jsly,c.msajsl,c.xzajsl,a.sxzy)  from EDlr a, EDljg b, SDlrAjsl c,SDlrFswx d ";
//				selectjpql_cout="select count(*) from EDlr a, EDljg b, SDlrAjsl c, SDlrFswx d";
//				//TODO 构造where条件和参数值
//				wherejpql = "a.pid = b.id and c.pid = a.id and d.pid = a.id and d.fl=(?1) and d.sl > 0";
//				queryParams.add(2);
//				flag = 2;
//			}
//			if(value.equals("zlxzss")){
//				wherejpql += " and a.zlxzss > (?"+ flag++ +") ";
//				queryParams.add(new Integer(0));
//			}
//			if(value.equals("zlmsss")){
//				wherejpql += " and a.zlmsss > (?"+ flag++ +") ";
//				queryParams.add(new Integer(0));
//			}
//			if(value.equals("zlqsjf")){
//				wherejpql += " and a.zlqsjf > (?"+ flag++ +") ";
//				queryParams.add(new Integer(0));
//			}
//			if(value.equals("zljs")){
//				wherejpql += " and a.zljs > (?"+ flag++ +") ";
//				queryParams.add(new Integer(0));
//			}
//			if(value.equals("zlfxpy")){
//				wherejpql += " and a.zlfxpy > (?"+ flag++ +") ";
//				queryParams.add(new Integer(0));
//			}
//			if(value.equals("zlyj")){
//				wherejpql += " and a.zlyj > (?"+ flag++ +") ";
//				queryParams.add(new Integer(0));
//			}
//			if(value.equals("zlyy")){
//				wherejpql += " and a.zlyy > (?"+ flag++ +") ";
//				queryParams.add(new Integer(0));
//			}
//			if(value.equals("zscqgb")){
//				wherejpql += " and a.zscqgb > (?"+ flag++ +") ";
//				queryParams.add(new Integer(0));
//			}
//			if(value.equals("xzzfaj")){
//				wherejpql += " and a.xzzfaj > (?"+ flag++ +") ";
//				queryParams.add(new Integer(0));
//			}
//			if(value.equals("zldh")){
//				wherejpql += " and a.zldh > (?"+ flag++ +") ";
//				queryParams.add(new Integer(0));
//			}
//		}
//		
//		
//		if(eparas.containsKey("dy")){
//			String value = (String) eparas.get("dy");
//			String[] dys = value.split("_");
//			String sdys = " and (";
//			for(String str : dys){
//				sdys += " b.dy like (?"+ flag++ +") or ";
//				queryParams.add("%" + str + "%");
//			}
//			
//			wherejpql += sdys.substring(0, sdys.lastIndexOf("or")) + ")";
//		}
//		if(eparas.containsKey("sl_zz")){
//			String value = (String) eparas.get("sl_zz");
//			wherejpql += " and a.zz = (?" + flag++ + ") ";
//			queryParams.add(Integer.parseInt(value.substring(0,1)));
//		}
//		if(eparas.containsKey("sl_gzwy")){
//			String value = (String) eparas.get("sl_gzwy");
//			String[] dys = value.split(";");
//			String sdys = " and (";
//			for(String str : dys){
//				
//				sdys += " a.wynl like (?"+ flag++ +") or ";
//				if(value.equals("1")){
//					queryParams.add("%" + "英语" + "%");
//				} else if(value.equals("2")){
//					queryParams.add("%" + "日语" + "%");
//				}else if(value.equals("3")){
//					queryParams.add("%" + "韩语" + "%");
//				}else if(value.equals("4")){
//					queryParams.add("%" + "德语" + "%");
//				}else if(value.equals("5")){
//					queryParams.add("%" + "法语" + "%");
//				}else if(value.equals("6")){
//					queryParams.add("%" + "" + "%");
//				}
//			}
//			wherejpql += sdys.substring(0, sdys.lastIndexOf("or")) + ")";
//		}
//		if(eparas.containsKey("sl_zynx")){
//			String value = (String) eparas.get("sl_zynx");
//			if(value.contains("lt")){
//				Calendar calendar = Calendar.getInstance(); 
//				Date date = calendar.getTime();
//				int year = calendar.get(Calendar.YEAR);
//				calendar.set(year - Integer.parseInt(value.substring(0,value.indexOf("lt"))), 0, 0);
//				wherejpql += " and a.dtGzjy < (?" + flag++ + ") ";
//				wherejpql += " and a.dtGzjy > (?" + flag++ + ") ";
//				queryParams.add(date);
//				queryParams.add(calendar.getTime());
//			}
//			if(value.contains("-")){
//				Calendar firstcalendar = Calendar.getInstance(); 
//				Calendar secondcalendar = Calendar.getInstance(); 
//				int year = firstcalendar.get(Calendar.YEAR);
//				firstcalendar.set(year - Integer.parseInt(value.split("-")[1]), 0, 0);
//				secondcalendar.set(year - Integer.parseInt(value.split("-")[0]), 0, 0);
//				
//				wherejpql += " and a.dtGzjy <= (?" + flag++ + ") ";
//				wherejpql += " and a.dtGzjy > (?" + flag++ + ") ";
//				queryParams.add(secondcalendar.getTime());
//				queryParams.add(firstcalendar.getTime());
//			}
//			if(value.contains("gt")){
//				Calendar calendar = Calendar.getInstance(); 
//				Date date = calendar.getTime();
//				int year = calendar.get(Calendar.YEAR);
//				calendar.set(year - Integer.parseInt(value.substring(0,value.indexOf("gt"))), 0, 0);
//				wherejpql += " and a.dtGzjy <= (?" + flag++ + ") ";
//				queryParams.add(calendar.getTime());
//				
//			}
//			
//		}
//		
//	
//		if(eparas.containsKey("name")){
//			String value = (String) eparas.get("name");
//			wherejpql += " and a.xm like  (?"+ flag++ +") ";
//			queryParams.add("%"+value.trim()+"%");
//		}
//		if(eparas.containsKey("sl_pjqlx")){
//			String value = (String) eparas.get("sl_pjqlx");
//			if(value.contains("lt")){
//				wherejpql += " and d.sl < (?" + flag++ + ") ";
//				queryParams.add(Integer.parseInt(value.substring(0,value.indexOf("lt"))));
//			}
//			if(value.contains("-")){
//				wherejpql += " and d.sl >= (?" + flag++ + ") ";
//				wherejpql += " and d.sl < (?" + flag++ + ") ";
//				queryParams.add(Integer.parseInt(value.split("-")[0]));
//				queryParams.add(Integer.parseInt(value.split("-")[1]));
//			}
//			if(value.contains("gt")){
//				wherejpql += " and d.sl >= (?" + flag++ + ") ";
//				queryParams.add(Integer.parseInt(value.substring(0,value.indexOf("gt"))));
//			}
//		}
//		
//		if(eparas.containsKey("sl_smsys")){
//			String value = (String) eparas.get("sl_smsys");
//			if(value.contains("lt")){
//				wherejpql += " and d.sl < (?" + flag++ + ") ";
//				queryParams.add(Integer.parseInt(value.substring(0,value.indexOf("lt"))));
//			}
//			if(value.contains("-")){
//				wherejpql += " and d.sl >= (?" + flag++ + ") ";
//				wherejpql += " and d.sl < (?" + flag++ + ") ";
//				queryParams.add(Integer.parseInt(value.split("-")[0]));
//				queryParams.add(Integer.parseInt(value.split("-")[1]));
//			}
//			if(value.contains("gt")){
//				wherejpql += " and d.sl >= (?" + flag++ + ") ";
//				queryParams.add(Integer.parseInt(value.substring(value.indexOf("gt"))));
//			}
//		}
//		
//		if(eparas.containsKey("sl_dll")){
//			String value = (String) eparas.get("sl_dll");
//			if(value.contains("lt")){
//				wherejpql += " and d.sl < (?" + flag++ + ") ";
//				queryParams.add(Integer.parseInt(value.substring(0,value.indexOf("lt"))));
//			}
//			if(value.contains("-")){
//				wherejpql += " and d.sl >= (?" + flag++ + ") ";
//				wherejpql += " and d.sl < (?" + flag++ + ") ";
//				queryParams.add(Integer.parseInt(value.split("-")[0]));
//				queryParams.add(Integer.parseInt(value.split("-")[1]));
//			}
//			if(value.contains("gt")){
//				wherejpql += " and d.sl >= (?" + flag++ + ") ";
//				queryParams.add(Integer.parseInt(value.substring(0,value.indexOf("gt"))));
//			}
//		}
//		
//		
//	System.out.println(selectjpql  +   wherejpql);
//		String orderby=null;
//		if(sortType!=null){
//			if(sortType.equals("auto")){
//				orderby = "a.id";
//			}else{
//				if(bAsc){
//					orderby = sortType;
//				}else{
//					orderby = sortType +" desc";
//				}
//			}
//		}
//		QueryResult<Map> qr = dao.getScrollData(selectjpql, selectjpql_cout, 
//				(pageNumber-1)*pageSize, pageSize, wherejpql, queryParams.toArray(), orderby);
//		Page p = new PageImpl(qr.getResultlist(), pageRequest, qr.getTotalrecord());
//		return p;
//	}
	
	
	public Page<LawsuitsDlrVO> getLawSuitsDlrPage(Map<String, Object> searchParams,Map<String,Object> eparas, 
			int pageNumber, int pageSize, String sortType,boolean bAsc)  {
		PageRequest pageRequest = buildPageRequest(pageNumber, pageSize, sortType, bAsc);
		StringBuffer sql = new StringBuffer("select a.id,a.xm,a.dt_gzjy,a.wynl,b.mc,b.dy,c.msajsl,c.xzajsl,a.sxzy from e_dlr a left join e_dljg b on a.pid = b.id left join s_dlr_ajsl c on c.pid = a.id left join s_dlr d on ");                            
		String count = "select count(*) from e_dlr a left join e_dljg b on a.pid = b.id left join s_dlr_ajsl c on c.pid = a.id left join s_dlr d on ";
		StringBuffer where = new StringBuffer(); 
		StringBuffer condition = new StringBuffer(" where 1=1 and a.zz != 0 ");
		String sfl = (String) searchParams.get("EQ_fl");
		//c.pid = a.id and c.fl = '100000' and c.nd = 0 
		where.append(" d.fl = " + sfl).append(" and  d.nd = 0 ").append(" and d.pid = a.id ");
		if(eparas.containsKey("dy")){
			String value = (String) eparas.get("dy");
			String[] dys = value.split("_");
			String sdys = " and (";
			for(String str : dys){
				sdys += " b.dy like ("+ "'%" + str + "%'" +") or ";
			}
			condition.append(sdys.substring(0, sdys.lastIndexOf("or")) + ")");
		}
		
		if(eparas.containsKey("name")){
		String value = (String) eparas.get("name");
		condition.append(" and a.xm like " + "'%" + value.trim() + "%'" );
		}

		if(eparas.containsKey("sl_zynx")){
			String value = (String) eparas.get("sl_zynx");
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			if(value.contains("lt")){
				Calendar calendar = Calendar.getInstance(); 
				String first = sdf.format(calendar.getTime());
				condition.append(" and a.dt_gzjy < " + "'" + first + "'");
				int year = calendar.get(Calendar.YEAR);
				int month = calendar.get(Calendar.MONTH) + 1;
				calendar.set(year - Integer.parseInt(value.substring(0,value.indexOf("lt"))), month, 0);
				String second = sdf.format(calendar.getTime());
				condition.append(" and a.dt_gzjy > " + "'"+second +"'");
			}else if(value.contains("-")){
				Calendar firstcalendar = Calendar.getInstance(); 
				Calendar secondcalendar = Calendar.getInstance(); 
				int year = firstcalendar.get(Calendar.YEAR);
//				int month = firstcalendar.get(Calendar.MONTH) + 1;
//				int day = firstcalendar.get(Calendar.DAY_OF_MONTH);
				firstcalendar.set(year - Integer.parseInt(value.split("-")[1]), 0, 0);
				secondcalendar.set(year - Integer.parseInt(value.split("-")[0]), 0, 0);
				String first = sdf.format(firstcalendar.getTime());
				String second = sdf.format(secondcalendar.getTime());
				condition.append(" and a.dt_gzjy > " + "'" + first + "'");
				condition.append(" and a.dt_gzjy <= " + "'"+second +"'");
			}else if(value.contains("gt")){
				Calendar calendar = Calendar.getInstance(); 
				int year = calendar.get(Calendar.YEAR);
				calendar.set(year - Integer.parseInt(value.substring(0,value.indexOf("gt"))), 0, 0);
				String first = sdf.format(calendar.getTime());
				condition.append(" and a.dt_gzjy <= " + "'" + first + "'");
			}
			
		}
		if(eparas.containsKey("sl_zz")){
			String value = (String) eparas.get("sl_zz");
			condition.append(" and a.zz = " + value);
		}
		if(eparas.containsKey("sl_gzwy")){
		String value = (String) eparas.get("sl_gzwy");
		if(value.equals("1")){
			condition.append(" and a.wynl like ("+ "'%" + "英语" + "%'" +") ");
		} else if(value.equals("2")){
			condition.append(" and a.wynl like ("+ "'%" + "日语" + "%'" +") ");
		}else if(value.equals("3")){
			condition.append(" and a.wynl like ("+ "'%" + "韩语" + "%'" +") ");
		}else if(value.equals("4")){
			condition.append(" and a.wynl like ("+ "'%" + "德语" + "%'" +") ");
		}else if(value.equals("5")){
			condition.append(" and a.wynl like ("+ "'%" + "法语" + "%'" +") ");
		}else if(value.equals("6")){
			condition.append(" and a.wynl like ("+ "'%" + "其他" + "%'" +") ");
		}
	}
		
		if(eparas.containsKey("sl_fwlx")){
			String value = (String) eparas.get("sl_fwlx");
			if(value.equals("zlsq")){
				
				
			}
			if(value.equals("zlfs")){
				sql = null;
				count = null;
				where = null;
				sql = new StringBuffer("select a.id,a.xm,a.dt_gzjy,a.wynl,b.mc,b.dy,c.msajsl,c.xzajsl,a.sxzy from e_dlr a LEFT join e_dljg b on a.pid = b.id left join s_dlr_ajsl c on c.pid = a.id left join s_dlr_fswx d on ");                            
				count = "select count(*) from e_dlr a left join e_dljg b on a.pid = b.id left join s_dlr_ajsl c on c.pid = a.id left join s_dlr_fswx d on ";
				where = new StringBuffer(" d.pid = a.id and d.fl= 1 ");
				condition.append(" and d.sl > 0 ");
				
			}
			if(value.equals("zlwx")){
				sql = null;
				count = null;
				where = null;
				sql = new StringBuffer("select a.id,a.xm,a.dt_gzjy,a.wynl,b.mc,b.dy,c.msajsl,c.xzajsl,a.sxzy from e_dlr a left join e_dljg b on a.pid = b.id left join s_dlr_ajsl c on c.pid = a.id left join s_dlr_fswx d on ");                            
				count = "select count(*) from e_dlr a left join e_dljg b on a.pid = b.id left join s_dlr_ajsl c on c.pid = a.id left join s_dlr d on ";
				where = new StringBuffer("  d.pid = a.id and d.fl= 2 ");
				condition.append(" and d.sl > 0 ");
			}
			if(value.equals("zlxzss")){
				condition.append(" and c.xzajsl > 0  ");
			}
			if(value.equals("zlmsss")){
				condition.append(" and c.msajsl > 0  ");
			}
			if(value.equals("zlqsjf")){
				condition.append(" and a.zlqsjf > " + '0');
			}
			if(value.equals("xzzfaj")){
				condition.append(" and a.xzzfaj > " + '0');
			}
			if(value.equals("zljs")){
				condition.append(" and a.zljs > " + '0');
			}
			if(value.equals("zlfxpy")){
				condition.append(" and a.zlfxpj > " + '0');
			}
			if(value.equals("zlyj")){
				condition.append(" and a.zlyj > " + '0');
			}
			if(value.equals("zlyy")){
				condition.append(" and a.zlyy > " + '0');
			}
			if(value.equals("zldh")){
				condition.append(" and a.zldh > " + '0');
			}
			if(value.equals("zscqgb")){
				condition.append(" and a.zscqgb > " + '0');
			}
		}
		if(eparas.containsKey("sl_pjqlx")){
			String value = (String) eparas.get("sl_pjqlx");
			if(value.contains("lt")){
				condition.append(" and d.sl < " + value.substring(0,value.indexOf("lt")));
			}
			if(value.contains("-")){
				condition.append(" and d.sl >= " + value.split("-")[0]);
				condition.append(" and d.sl < " + value.split("-")[1]);
			}
			if(value.contains("gt")){
				condition.append(" and d.sl >= " + value.substring(0,value.indexOf("gt")));
			}
		}
		
		if(eparas.containsKey("sl_smsys")){
			String value = (String) eparas.get("sl_smsys");
			if(value.contains("lt")){
				condition.append(" and d.sl < " + value.substring(0,value.indexOf("lt")));
			}
			if(value.contains("-")){
				condition.append(" and d.sl >= " + value.split("-")[0]);
				condition.append(" and d.sl < " + value.split("-")[1]);
			}
			if(value.contains("gt")){
				condition.append(" and d.sl >= " + value.substring(0,value.indexOf("gt")));
			}
		}
		
		
		if(eparas.containsKey("sl_dll")){
			String value = (String) eparas.get("sl_dll");
			if(value.contains("lt")){
				if(eparas.containsKey("sl_fwlx")){
					String temp = (String) eparas.get("sl_fwlx");
					if(temp.equals("zlxzss")){
						 condition.append(" and c.xzajsl < " + value.substring(0,value.indexOf("lt")));
					}else if(temp.equals("zlmsss")){
						 condition.append(" and c.msajsl < " + value.substring(0,value.indexOf("lt")));
					}else{
						condition.append(" and d.sl < " + value.substring(0,value.indexOf("lt")));
					}
				}else{
					condition.append(" and d.sl < " + value.substring(0,value.indexOf("lt")));
				}
			}
			if(value.contains("-")){
				if(eparas.containsKey("sl_fwlx")){
					String temp = (String) eparas.get("sl_fwlx");
					if(temp.equals("zlxzss")){
						 condition.append(" and c.xzajsl >= " + value.split("-")[0]);
						 condition.append(" and c.xzajsl < " + value.split("-")[1]);
					}else if(temp.equals("zlmsss")){
						 condition.append(" and c.msajsl >= " + value.split("-")[0]);
						 condition.append(" and c.msajsl < " + value.split("-")[1]);
					}else{
						condition.append(" and d.sl >= " + value.split("-")[0]);
						condition.append(" and d.sl < " + value.split("-")[1]);
					}
				}else{ 
					condition.append(" and d.sl >= " + value.split("-")[0]);
					condition.append(" and d.sl < " + value.split("-")[1]);
				}
			}
			if(value.contains("gt")){
				if(eparas.containsKey("sl_fwlx")){
					String temp = (String) eparas.get("sl_fwlx");
					if(temp.equals("zlxzss")){
						condition.append(" and c.xzajsl >= " + value.substring(0,value.indexOf("gt")));
					}else if(temp.equals("zlmsss")){
						 condition.append(" and c.msajsl >= " + value.substring(0,value.indexOf("gt")));
					}else{
						condition.append(" and d.sl >= " + value.substring(0,value.indexOf("gt")));
					}
				}else{
				condition.append(" and d.sl >= " + value.substring(0,value.indexOf("gt")));
				}
				
			}
		}

		
		
	//System.out.println(selectjpql  +   wherejpql);
		String orderby=null;
		if(sortType!=null){
			if(sortType.equals("auto")){
				orderby = "a.id";
			}else{
				if(bAsc){
					orderby = sortType;
					if(sortType.equals("a.dt_gzjy")){
						orderby = sortType +" desc";
					}
				}else{
					orderby = sortType +" desc";
					if(sortType.equals("a.dt_gzjy")){
						orderby = sortType;
					}
				}
			}
		}
		QueryResult<LawsuitsDlrVO> qr = dao.getScrollDataForLawDlr(sql.toString(),count,where.toString(),(pageNumber-1)*pageSize, pageSize,condition.toString(),orderby);
		Page p = new PageImpl(qr.getResultlist(), pageRequest, qr.getTotalrecord());
		return p;
	}
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	public Page<EDlr> getEDlrEntities(int page, int start, int limit, String filter, String sort, int userSsjg)
			throws Exception {
		ExtReq parser = new ExtReq();
		Map<String, SearchFilter> mf = parser.parseFilter(filter);
		Sort ms = parser.parseSort(sort);
		mf.put("pid", new SearchFilter("pid", Operator.EQ, userSsjg));
		PageRequest pageRequest = new PageRequest(page - 1, limit, ms);
		Specification<EDlr> spec = DynamicSpecifications.bySearchFilter(mf.values(), EDlr.class);
		Page<EDlr> r = dlrDao.findAll(spec, pageRequest);
		return r;
	}
	
	/**
	 * 创建分页请求.
	 */
	private PageRequest buildPageRequest(int pageNumber, int pagzSize, String sortType, boolean bAsc) {
		Sort sort = null;
		if ("auto".equals(sortType)) {
			sort = new Sort(Direction.DESC, "id");
		} else {
			if (bAsc) {
				sort = new Sort(Direction.ASC, sortType);
			} else {
				sort = new Sort(Direction.DESC, sortType);
			}

		}

		return new PageRequest(pageNumber - 1, pagzSize, sort);
	}
	 
	/**
     * 代理人详细页-累计代理案件数量数据
     */
	public Map<String,Object> getSDlrInfo(long id){	
		Map<String,Object> mf = new HashMap<String,Object>();
		int sql = 0,qls = 0,sms = 0;//授权量、平均权利要求项数、平均说明书页数		
		int fmgn = 0,fmgw = 0,fmgat = 0;  //发明(国内、国外、港澳台)
		int syxxgn = 0,syxxgw = 0,syxxgat = 0;//实用新型(国内、国外、港澳台)
		int wggn = 0,wggw = 0,wggat = 0;      //外观设计(国内、国外、港澳台)
		int fmsql = 0,syxxsql = 0,wgsql = 0;  //授权（发明、实用新型、外观）
		List<SDlr> objlist = dao.queryByWhere(SDlr.class, " pid=? and (fl LIKE '101%' or fl LIKE '105%' OR fl LIKE '106%' or fl LIKE '10011%' or fl LIKE '10012%' OR fl LIKE '10013%' or fl LIKE '10021%' or fl LIKE '10022%' OR fl LIKE '10023%' or fl LIKE '10031%' or fl LIKE '10032%' OR fl LIKE '10033%') ", new Object[] { (int) id });
		for(SDlr obj : objlist) {
			String fl = obj.getFl();
			int sl = obj.getSl();
			if(fl.indexOf("101") != -1){
				sql += sl;
				if(fl.indexOf("10110") != -1){
					fmsql += sl;
				}else if(fl.indexOf("10120") != -1){
					syxxsql += sl;
				}else if(fl.indexOf("10130") != -1){
					wgsql += sl;
				}
			}else if(fl.indexOf("105") != -1){
				qls += sl;
			}else if(fl.indexOf("106") != -1){
				sms += sl;
			}else if(fl.indexOf("10011") != -1){
				fmgn += sl;
			}else if(fl.indexOf("10012") != -1){
				fmgw += sl;
			}else if(fl.indexOf("10013") != -1){
				fmgat += sl;
			}else if(fl.indexOf("10021") != -1){
				syxxgn += sl;
			}else if(fl.indexOf("10022") != -1){
				syxxgw += sl;
			}else if(fl.indexOf("10023") != -1){
				syxxgat += sl;
			}else if(fl.indexOf("10031") != -1){
				wggn += sl;
			}else if(fl.indexOf("10032") != -1){
				wggw += sl;
			}else if(fl.indexOf("10033") != -1){
				wggat += sl;
			}
		}
		mf.put("sql", sql);
		mf.put("qls", qls);
		mf.put("sms", sms);
		mf.put("fmgn", fmgn);
		mf.put("fmgw", fmgw);
		mf.put("fmgat", fmgat);
		mf.put("syxxgn", syxxgn);
		mf.put("syxxgw", syxxgw);
		mf.put("syxxgat", syxxgat);
		mf.put("wggn", wggn);
		mf.put("wggw", wggw);
		mf.put("wggat", wggat);
		mf.put("fmsql", fmsql);
		mf.put("syxxsql", syxxsql);
		mf.put("wgsql", wgsql);
		return 	mf;	
	}
	/**
     * 代理人详细页-累计代理案件数量区块-专利行政诉讼\专利民事诉讼
     */
	public Map<String,Object> getSDlrAjslInfo(long id){	
		Map<String,Object> mf = new HashMap<String,Object>();
		int msajsl = 0,xzajsl = 0;//专利行政诉讼\专利民事诉讼	
		SDlrAjsl obj = dao.findByWhere(SDlrAjsl.class, " pid=? ", new Object[] { (int) id });
		if(obj != null){
			msajsl = obj.getMsajsl();
			xzajsl = obj.getXzajsl();
		}
		mf.put("msajsl", msajsl);
		mf.put("xzajsl", xzajsl);
		return 	mf;	
	}
	/**
     * 代理人详细页-累计代理案件数量区块-专利复审\专利无效宣告
     */
	public Map<String,Object> getSDlrFswxInfo(long id){	
		Map<String,Object> mf = new HashMap<String,Object>();
		int fs = 0,wx = 0;  //专利复审\专利无效宣告	
		List<SFswx> objlist = dao.queryByWhere(SFswx.class, " pidDlr=? ", new Object[] { (int) id });
		for(SFswx obj : objlist) {
			int fl = obj.getFl();
			if(fl == 1){
				fs += 1;
			}else if(fl == 2){
				wx += 1;
			}
		}
		mf.put("fs", fs);
		mf.put("wx", wx);
		return 	mf;	
	}
	/**
	 * 填报代理人保存
	 * @param id
	 * @param rmap
	 * @return
	 * @throws Exception
	 */
	public EDlrSh updateEDlrSh(Long id, Map<String, Object> rmap) throws Exception {
		User usr = getCurrentUser();
		Integer sh = (Integer)rmap.get(PN_SHZT);
		String ur = usr.getRoles();
		setSh(ur, sh, rmap);
		EDlrSh e1 =  (EDlrSh)dao.update(EN_DLRSH, id, rmap);
		if(ur.equals(ApiController.ROLE_ADMIN_XH) 
				|| ur.equals(ApiController.ROLE_ADMIN)){
			//协议管理员或者超级管理员, 如果审核通过,执行clone动作
			sh = e1.getShzt();
			if(sh!=null && sh==4){
				cloneSh(e1);
			}
		}
		return e1;
	}
	public EDlr updateEDlr(Long id, Map<String, Object> rmap) throws Exception {
		EDlr edlr =  (EDlr)dao.update("EDlr", id, rmap);
		cloneEDlrToSh(edlr);
		return edlr;
	}
	
	/**
	 * 获得当前登录用户
	 * @return
	 * @author wangshuxin
	 * 2016-04-18
	 */
	private User getCurrentUser() {
		ShiroUser shiroUser = (ShiroUser) SecurityUtils.getSubject().getPrincipal();
		User user = dao.findByWhere(User.class,"loginName=?",new Object[]{shiroUser.loginName});
		return user;
	}
	private void setSh(String urole,Integer shzt, Map<String, Object> rmap){
		//如果是机构管理员,审核状态除非为‘填报完毕’，否则将其改为‘正在填报’
		User usr = getCurrentUser();
		Integer sh = (Integer)rmap.get(PN_SHZT);
		String ur = usr.getRoles();
		if(ur.equals(ApiController.ROLE_ADMIN_JG)){
			if(sh==null || sh!=2){
				//modify by wsx 20160419 begin
				//rmap.put(PN_SHZT, 2);
				rmap.put(PN_SHZT, 1);
				//modify by wsx 20160419 end
			}			
		}
	}
	
	private void cloneSh(EDlrSh e1) throws InstantiationException, IllegalAccessException{
		EDlr e2 = dao.findByWhere(EDlr.class, "eid=?",new Object[]{e1.getId().intValue()});
		if(e2==null){
			e2 = EDlr.class.newInstance();
			BeanUtils.copyProperties(e1, e2);
			e2.setId(null);
			e2.setEid(e1.getId().intValue());
			dao.persist(e2);
		}else{
			BeanUtils.copyProperties(e1, e2);
			dao.update(e2);			
		}
	}
	/**
	 * 克隆代理人到填报代理人
	 * @param dljg
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 * @author wangshuxin
	 * 2016-04-20
	 */
	private void cloneEDlrToSh(EDlr dlr) throws InstantiationException, IllegalAccessException{
		EDlrSh dlrsh = dao.find(EDlrSh.class, Long.parseLong(String.valueOf(dlr.getEid())));
		if(dlrsh==null){
			dlrsh = EDlrSh.class.newInstance();
			BeanUtils.copyProperties(dlr, dlrsh);
			dlrsh.setId(null);
			dao.persist(dlrsh);
		}else{
			BeanUtils.copyProperties(dlr, dlrsh);
			dao.update(dlrsh);			
		}
	}
}
